<%@page import="cn.sendto.model.StudentEntity"%>
<%@page import="cn.sendto.mapper.StudentMapper"%>
<%@page import="cn.sendto.model.CourseEntity"%>
<%@page import="cn.sendto.mapper.CourseMapper"%>
<%@page import="cn.sendto.model.ScoreEntity"%>
<%@page import="java.util.List"%>
<%@page import="cn.sendto.mapper.ScoreMapper"%>
<%@page import="cn.sendto.mapper.IRowMapper"%>
<%@page import="cn.sendto.dbhelper.DBHelper"%>
<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Insert title here</title>
</head>
<style type="text/css">
			html,body{
				width: 100%;
				height: 100%;
				background-color: #DDDDDD;
			}
			.con{
				width: 95%;
				margin: 0 auto;
				border: 1px solid darkgrey;
				margin-top:3%;
			}
			.title{
				height: 30px;
				border-bottom: 1px dashed darkgray;
			}
			.title li{
				float: left;
				width: 14%;
				line-height: 30px;
				height: 30px;
				text-align: center;
				overflow: hidden;
			}
			
			a:hover{
				left: 1px;
				top: 1px;
				position: relative;
				color: blue;
				text-shadow:  #000000;
				color: royalblue;
			}
			ul:last-child{
				border-bottom: none;
			}
			ul:first-child{
			   font-weight: bold;
			   background-color: #CCC;
			}
			.cx{
			width:95%;
			margin-left:100px;
				
			}
			select,input{
			width:100px;
			font-size:20px;
			}
		</style>
		<%
			if(request.getAttribute("message")!=null){
			%>
				<script type="text/javascript">
					alert('<%=request.getAttribute("message")%>');
				</script>
			<% 
			}
			%>
<body>
	<h4 style="text-align: center; margin-top: 3%;font-size:30px;">成绩信息列表</h4>
	<div class="cx">
	<form action="doscore.jsp" method="post">
	<%
			request.setCharacterEncoding("utf-8");
			DBHelper helper1 = new DBHelper();
			IRowMapper mapper1 = new CourseMapper();
			String sql1 = "select * from courseinfo ";
			List<CourseEntity> list1 = helper1.executeQuery(sql1, mapper1, null);
			%>
			<span>科目</span><select name="coursename">
					<option value="all">全部</option>
				<%
				for(int a =0;a<list1.size();a++){
				out.print("<option value='"+list1.get(a).getCourseId()+"'>"+list1.get(a).getCourseName()+"</option>");
				}
				%>
				</select>
				<%
				IRowMapper mapper2 = new StudentMapper();
				String sql2 = "select * from studentinfo";
				List<StudentEntity> list2 = helper1.executeQuery(sql2, mapper2, null);    						
				%>
				<span>学生</span><select name="studentname">
						<option value="all">全部</option>
				<%
				for(int b =0;b<list2.size();b++){
				out.print("<option value='"+list2.get(b).getStudentId()+"'>"+list2.get(b).getStudentName()+"</option>");
				}
				%>		
				</select>	
			<input type="submit"  value="查询" />
		</form>
	</div>
		<div class="con">
			<ul class="title">
				<li>序号</li>
				<li>姓名</li>				
				<li>科目</li>
				<li>成绩</li>
				<li>修改</li>
				<li>删除</li>
			</ul>
			<%
	request.setCharacterEncoding("UTF-8");
	String id = request.getParameter("coursename");
	String stuid = request.getParameter("studentname");
	//实例化操作数据库的类DBHelper
	DBHelper helper = new DBHelper();
	IRowMapper mapper = new ScoreMapper();
	String sql ="";
	List<ScoreEntity> list ;
	if(id.equals("all") && stuid.equals("all")){
	sql="select scoreinfo.*,courseinfo.coursename,studentinfo.studentname from scoreinfo,courseinfo,studentinfo where scoreinfo.courseid=courseinfo.courseid and scoreinfo.studentid=studentinfo.studentid ";
	list=helper.executeQuery(sql, mapper,null);
	
}else if(id.equals("all")){	
	sql="select scoreinfo.*,courseinfo.coursename,studentinfo.studentname from scoreinfo,courseinfo,studentinfo where scoreinfo.courseid=courseinfo.courseid and scoreinfo.studentid=studentinfo.studentid and scoreinfo.studentid=?";
	list=helper.executeQuery(sql, mapper, stuid);
	
}else if(stuid.equals("all")){
	sql="select scoreinfo.*,courseinfo.coursename,studentinfo.studentname from scoreinfo,courseinfo,studentinfo where scoreinfo.courseid=courseinfo.courseid and scoreinfo.studentid=studentinfo.studentid and scoreinfo.courseid=?";
	list=helper.executeQuery(sql, mapper, id);
	}else{
	sql="select scoreinfo.*,courseinfo.coursename,studentinfo.studentname from scoreinfo,courseinfo,studentinfo where scoreinfo.courseid=courseinfo.courseid and scoreinfo.studentid=studentinfo.studentid and scoreinfo.courseid=? and scoreinfo.studentid=?";
	list=helper.executeQuery(sql, mapper,id,stuid);
	
}%>

<% 
	for(int i=0;i<list.size();i++){		
		%>		
				<ul class="title">
				<li><%=i+1 %></li>
				<li><%=list.get(i).getStudentName() %></li>
				<li><%=list.get(i).getCourseName()%></li>
				<li><%=list.get(i).getScore() %></li>
				<li><a href="modifyscore.jsp?id=<%=list.get(i).getScoreId()%>">修改</a></li>
				<li><a href="descore.jsp?id=<%=list.get(i).getScoreId()%>">删除</a></li>
			</ul>
		<%
			}
	helper.close();
%>	
</div>		
</body>
</html>




    
    
    
   